package dao

import (
	"bookmark-web/models"
)

// CreateBookmark 创建网址收藏
func CreateBookmark(bookmark *models.Bookmark) error {
	// INSERT INTO bookmarks (title, url, description, favicon, category_id, user_id, visit_count, created_at, updated_at)
	// VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
	return DB.Create(bookmark).Error
}

// GetBookmarkByID 根据ID获取网址收藏
func GetBookmarkByID(id uint, userID uint) (*models.Bookmark, error) {
	var bookmark models.Bookmark
	// SELECT * FROM bookmarks WHERE id = ? AND user_id = ? LIMIT 1
	err := DB.Where("id = ? AND user_id = ?", id, userID).
		Preload("Category").
		First(&bookmark).Error
	return &bookmark, err
}

// GetBookmarksByUserID 根据用户ID获取网址收藏列表（分页）
func GetBookmarksByUserID(userID uint, page, pageSize int, search string, categoryID *uint) ([]*models.Bookmark, int64, error) {
	var bookmarks []*models.Bookmark
	var total int64

	// SELECT * FROM bookmarks WHERE user_id = ? [AND category_id = ?] [AND (title LIKE ? OR description LIKE ? OR url LIKE ?)]
	query := DB.Model(&models.Bookmark{}).Where("user_id = ?", userID)

	// 如果指定了分类ID，筛选该分类下的收藏
	if categoryID != nil {
		query = query.Where("category_id = ?", *categoryID)
	}

	// 如果有搜索关键词，添加模糊搜索
	if search != "" {
		query = query.Where("title LIKE ? OR description LIKE ? OR url LIKE ?",
			"%"+search+"%", "%"+search+"%", "%"+search+"%")
	}

	// 获取总数
	// SELECT COUNT(*) FROM bookmarks WHERE user_id = ? [AND category_id = ?] [AND (title LIKE ? OR description LIKE ? OR url LIKE ?)]
	err := query.Count(&total).Error
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	// SELECT * FROM bookmarks WHERE user_id = ? [AND category_id = ?] [AND (title LIKE ? OR description LIKE ? OR url LIKE ?)]
	// ORDER BY created_at DESC LIMIT ? OFFSET ?
	offset := (page - 1) * pageSize
	err = query.Preload("Category").
		Offset(offset).
		Limit(pageSize).
		Order("created_at DESC").
		Find(&bookmarks).Error
	if err != nil {
		return nil, 0, err
	}

	return bookmarks, total, nil
}

// GetAllBookmarksByUserID 根据用户ID获取所有网址收藏
func GetAllBookmarksByUserID(userID uint) ([]*models.Bookmark, error) {
	var bookmarks []*models.Bookmark

	// SELECT * FROM bookmarks WHERE user_id = ? ORDER BY created_at DESC
	// SELECT * FROM bookmarks WHERE user_id = ? ORDER BY created_at DESC
	err := DB.Where("user_id = ?", userID).
		Preload("Category").
		Order("created_at DESC").
		Find(&bookmarks).Error

	return bookmarks, err
}

// GetBookmarksByCategoryID 根据分类ID获取网址收藏列表
func GetBookmarksByCategoryID(categoryID uint, userID uint) ([]*models.Bookmark, error) {
	var bookmarks []*models.Bookmark
	// SELECT * FROM bookmarks WHERE category_id = ? AND user_id = ? ORDER BY created_at DESC
	err := DB.Where("category_id = ? AND user_id = ?", categoryID, userID).
		Preload("Category").
		Order("created_at DESC").
		Find(&bookmarks).Error
	return bookmarks, err
}

// UpdateBookmark 更新网址收藏
func UpdateBookmark(bookmark *models.Bookmark) error {
	// UPDATE bookmarks SET title = ?, url = ?, description = ?, favicon = ?, category_id = ?, user_id = ?,
	// visit_count = ?, updated_at = NOW() WHERE id = ?

	// 使用Select明确指定要更新的字段，避免GORM处理关联关系导致的问题
	return DB.Model(bookmark).Select(
		"title",
		"url",
		"description",
		"favicon",
		"category_id",
		"user_id",
		"visit_count",
	).Updates(bookmark).Error
}

// DeleteBookmark 删除网址收藏
func DeleteBookmark(id uint, userID uint) error {
	// DELETE FROM bookmarks WHERE id = ? AND user_id = ?
	return DB.Where("id = ? AND user_id = ?", id, userID).Delete(&models.Bookmark{}).Error
}

// BatchDeleteBookmarks 批量删除网址收藏
func BatchDeleteBookmarks(bookmarkIDs []uint, userID uint) error {
	// DELETE FROM bookmarks WHERE id IN (?) AND user_id = ?
	return DB.Where("id IN ? AND user_id = ?", bookmarkIDs, userID).Delete(&models.Bookmark{}).Error
}

// BatchMoveBookmarks 批量移动网址收藏到指定分类
func BatchMoveBookmarks(bookmarkIDs []uint, categoryID *uint, userID uint) error {
	// UPDATE bookmarks SET category_id = ? WHERE id IN (?) AND user_id = ?
	return DB.Model(&models.Bookmark{}).
		Where("id IN ? AND user_id = ?", bookmarkIDs, userID).
		Update("category_id", categoryID).Error
}

// SearchBookmarks 搜索网址收藏
func SearchBookmarks(userID uint, keyword string, page, pageSize int) ([]*models.Bookmark, int64, error) {
	var bookmarks []*models.Bookmark
	var total int64

	// SELECT * FROM bookmarks WHERE user_id = ? AND (title LIKE ? OR description LIKE ? OR url LIKE ?)
	query := DB.Model(&models.Bookmark{}).Where("user_id = ? AND (title LIKE ? OR description LIKE ? OR url LIKE ?)",
		userID, "%"+keyword+"%", "%"+keyword+"%", "%"+keyword+"%")

	// 获取总数
	// SELECT COUNT(*) FROM bookmarks WHERE user_id = ? AND (title LIKE ? OR description LIKE ? OR url LIKE ?)
	err := query.Count(&total).Error
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	// SELECT * FROM bookmarks WHERE user_id = ? AND (title LIKE ? OR description LIKE ? OR url LIKE ?)
	// ORDER BY created_at DESC LIMIT ? OFFSET ?
	offset := (page - 1) * pageSize
	err = query.Preload("Category").
		Offset(offset).
		Limit(pageSize).
		Order("created_at DESC").
		Find(&bookmarks).Error

	return bookmarks, total, err
}

// IncrementVisitCount 增加访问次数
func IncrementVisitCount(id uint, userID uint) error {
	// UPDATE bookmarks SET visit_count = visit_count + 1 WHERE id = ? AND user_id = ?
	return DB.Model(&models.Bookmark{}).
		Where("id = ? AND user_id = ?", id, userID).
		UpdateColumn("visit_count", DB.Raw("visit_count + 1")).Error
}

// GetBookmarksByIDs 根据ID列表获取网址收藏
func GetBookmarksByIDs(bookmarkIDs []uint, userID uint) ([]*models.Bookmark, error) {
	var bookmarks []*models.Bookmark
	// SELECT * FROM bookmarks WHERE id IN (?) AND user_id = ?
	err := DB.Where("id IN ? AND user_id = ?", bookmarkIDs, userID).
		Preload("Category").
		Find(&bookmarks).Error
	return bookmarks, err
}

// CheckBookmarkExists 检查网址是否已存在
func CheckBookmarkExists(url string, userID uint, excludeID ...uint) (bool, error) {
	// SELECT COUNT(*) FROM bookmarks WHERE url = ? AND user_id = ? [AND id != ?]
	query := DB.Model(&models.Bookmark{}).Where("url = ? AND user_id = ?", url, userID)

	// 如果是更新操作，排除当前收藏ID
	if len(excludeID) > 0 {
		query = query.Where("id != ?", excludeID[0])
	}

	var count int64
	err := query.Count(&count).Error
	return count > 0, err
}

// GetBookmarkStats 获取收藏统计信息
func GetBookmarkStats(userID uint) (map[string]interface{}, error) {
	stats := make(map[string]interface{})

	// 总收藏数
	var totalCount int64
	// SELECT COUNT(*) FROM bookmarks WHERE user_id = ?
	err := DB.Model(&models.Bookmark{}).Where("user_id = ?", userID).Count(&totalCount).Error
	if err != nil {
		return nil, err
	}
	stats["total_bookmarks"] = totalCount

	// 分类统计
	// SELECT bookmarks.category_id, COALESCE(categories.name, '未分类') as category_name, COUNT(*) as count
	// FROM bookmarks LEFT JOIN categories ON bookmarks.category_id = categories.id
	// WHERE bookmarks.user_id = ? GROUP BY bookmarks.category_id, categories.name
	var categoryStats []struct {
		CategoryID   *uint  `json:"category_id"`
		CategoryName string `json:"category_name"`
		Count        int64  `json:"count"`
	}

	err = DB.Model(&models.Bookmark{}).
		Select("bookmarks.category_id, COALESCE(categories.name, '未分类') as category_name, COUNT(*) as count").
		Joins("LEFT JOIN categories ON bookmarks.category_id = categories.id").
		Where("bookmarks.user_id = ?", userID).
		Group("bookmarks.category_id, categories.name").
		Scan(&categoryStats).Error

	if err != nil {
		return nil, err
	}
	stats["category_stats"] = categoryStats

	return stats, nil
}

// GetRecentBookmarks 获取最近添加的收藏
func GetRecentBookmarks(userID uint, limit int) ([]*models.Bookmark, error) {
	// SELECT * FROM bookmarks WHERE user_id = ? ORDER BY created_at DESC LIMIT ?
	var bookmarks []*models.Bookmark
	err := DB.Where("user_id = ?", userID).
		Preload("Category").
		Order("created_at DESC").
		Limit(limit).
		Find(&bookmarks).Error
	return bookmarks, err
}

// GetPopularBookmarks 获取热门收藏（按访问次数排序）
func GetPopularBookmarks(userID uint, limit int) ([]*models.Bookmark, error) {
	var bookmarks []*models.Bookmark
	// SELECT * FROM bookmarks WHERE user_id = ? AND visit_count > 0 ORDER BY visit_count DESC, created_at DESC LIMIT ?
	err := DB.Where("user_id = ? AND visit_count > 0", userID).
		Preload("Category").
		Order("visit_count DESC, created_at DESC").
		Limit(limit).
		Find(&bookmarks).Error
	return bookmarks, err
}
